package com.jp.fm.importer;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.FileUtils;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.dao.AccountDao;
import com.jp.fm.data.Account;
import com.jp.fm.service.CategoryService;

public final class NTSCUImporter {

	private static final SimpleDateFormat SDF = new SimpleDateFormat(
			"MM/dd/yyyy");

	private NTSCUImporter() {
	}

	/**
	 * @param args
	 */
	public static void main(final String[] args) {

		String wholeFile = null;
		Integer insertCount = 0;
		CategoryService categoryService = new CategoryService();

		try {
			wholeFile = FileUtils.readFileToString(new File(args[0]));
		} catch (IOException e) {
			e.printStackTrace();
		}

		if (null != wholeFile) {

			CSVParser parser = null;
			try {
				parser = new CSVParser(wholeFile, CSVFormat.DEFAULT);
			} catch (IOException e1) {
				e1.printStackTrace();
			}

			if (null != parser) {

				for (Iterator<CSVRecord> iterator = parser.iterator(); iterator
						.hasNext();) {
					CSVRecord record = iterator.next();

					Connection c = null;
					PreparedStatement p = null;
					Date date = null;
					String acct = null;
					String desc = null;
					String extId = null;
					Float amount = null;

					try {

						date = SDF.parse(record.get(0));
						acct = args[1];
						desc = record.get(1);
						extId = record.get(2);

						Number number = NumberFormat.getInstance().parse(
								record.get(3));
						amount = number.floatValue();

						c = HomeFinanceRunner.getConnection();

						p = c.prepareStatement("INSERT INTO PUBLIC.TRANSACTIONS "
								+ "( DATE, ACCOUNT, AMOUNT, CATEGORY_ID, "
								+ "DESCRIPTION, EXTERNAL_ID) "
								+ "VALUES (?,?,?,?,?,?)");

						p.setDate(1, new java.sql.Date(date.getTime()));
						p.setString(2, acct);
						p.setFloat(3, amount);
						p.setInt(4, categoryService.getCategoryByKeyword(desc));
						p.setString(5, desc);
						p.setString(6, extId);

						p.executeUpdate();

						c.commit();
						insertCount++;

						// Update account balance
						AccountDao accountDao = new AccountDao();
						Account account = accountDao.findByName(acct);
						accountDao.writeObject(account);

					} catch (SQLException e) {
						System.out.println("acct = " + acct);
						System.out.println("date = " + date);
						System.out.println("desc = " + desc);
						System.out.println("amount = " + amount);
						System.out.println("extId = " + extId);
						e.printStackTrace();

					} catch (ParseException pe) {
						pe.printStackTrace();
					} catch (IOException e) {
						e.printStackTrace();
					} finally {

						HomeFinanceRunner.closeConnection(c, p, null);
					}
				}
			}
		}

		System.out.println(insertCount + " records inserted");
	}

}
